import os
import sqlite3
import hashlib
from typing import List, Tuple, Optional


def list_tables(conn: sqlite3.Connection) -> list[str]:
    cur = conn.execute("SELECT name FROM sqlite_master WHERE type='table'")
    return [r[0] for r in cur.fetchall()]


def get_sessions(session_db_path: str) -> List[Tuple[str, Optional[str], Optional[str]]]:
    """
    返回 [(username, display_name, summary)]
    兼容 SessionTable/Session/contact.
    """
    if not os.path.isfile(session_db_path):
        return []
    conn = sqlite3.connect(session_db_path)
    conn.row_factory = sqlite3.Row
    try:
        tables = set(list_tables(conn))
        # 优先 Session/SessionTable
        for t in ("Session", "SessionTable"):
            if t in tables:
                rows = conn.execute(f"SELECT * FROM {t} ORDER BY sort_timestamp DESC").fetchall()
                out = []
                for r in rows:
                    username = r.get("username") if isinstance(r, dict) else r[0]
                    if username is None:
                        try:
                            username = r["userName"]
                        except Exception:
                            username = None
                    display_name = None
                    try:
                        display_name = r["display_name"]
                    except Exception:
                        try:
                            display_name = r["nickName"]
                        except Exception:
                            display_name = None
                    summary = None
                    try:
                        summary = r["display_summary"]
                    except Exception:
                        summary = None
                    if username:
                        out.append((username, display_name, summary))
                return out
        # 回落 contact
        if "contact" in {t.lower() for t in tables}:
            rows = conn.execute("SELECT username, alias as display_name, remark as display_summary FROM contact").fetchall()
            return [(r[0], r[1], r[2]) for r in rows]
        return []
    finally:
        conn.close()


def _md5(s: str) -> str:
    return hashlib.md5(s.encode("utf-8")).hexdigest()


def _find_message_table(conn: sqlite3.Connection, username: str) -> Optional[str]:
    tables = [r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'Msg_%'")]
    if not tables:
        return None
    h = _md5(username)
    preferred = f"Msg_{h}"
    for t in tables:
        if t.lower() == preferred.lower():
            return t
    for t in tables:
        if h in t:
            return t
    for t in tables:
        if h[:24] in t:
            return t
    return None


def get_messages_for_session(message_db_path: str, username: str, limit: int = 100, offset: int = 0) -> list[dict]:
    if not os.path.isfile(message_db_path):
        return []
    conn = sqlite3.connect(message_db_path)
    conn.row_factory = sqlite3.Row
    try:
        table = _find_message_table(conn, username)
        if not table:
            return []
        # 列存在性探测
        cols = {r[1].lower() for r in conn.execute(f"PRAGMA table_info('{table}')").fetchall()}
        has_sort_seq = "sort_seq" in cols
        has_create_time = "create_time" in cols
        order_col = "sort_seq" if has_sort_seq else ("create_time" if has_create_time else "rowid")
        rows = conn.execute(
            f"SELECT * FROM {table} ORDER BY {order_col} DESC LIMIT ? OFFSET ?",
            (limit, offset),
        ).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


